package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.model.Users;
import com.util.DBUtil;


public class UsersDAO {
	private final DBUtil db=new DBUtil();

	public boolean insert(String name,String psw){
		boolean flag=false;
		Connection con=db.getConnection();
		String sql = "insert into Users (username,password) values(?,?)";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, name);
			st.setString(2, psw);
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	public boolean insert(Users gb){
		boolean flag=false;
		Connection con=db.getConnection();
		String sql = "insert into Users (username,password) values(?,?)";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, gb.getUsername());
			st.setString(2, gb.getPassword());
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	public boolean update(Users u){
		boolean flag=false;
		Connection con=db.getConnection();
		String sql="update Users set username=?,password=? where id=?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, u.getUsername());
			st.setString(2, u.getPassword());
			st.setInt(3, u.getId());
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	public boolean delete(Users u){
		boolean flag=false;
		Connection con=db.getConnection();
		String sql="delete from Users where id=?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, u.getId());
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	public boolean delete(int id){
		boolean flag=false;
		Connection con=db.getConnection();
		String sql="delete from Users where id=?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			int n=st.executeUpdate();
			if(n!=0)flag=true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}
	public Users get(int id){
		Users gb=new Users();
		Connection con=db.getConnection();
		String sql="select * from Users where id=?";
		PreparedStatement st = null;
		ResultSet rs=null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			rs=st.executeQuery();
			if(rs.next()){
				gb.setId(rs.getInt("id"));
				gb.setUsername(rs.getString("username"));
				gb.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return gb;
	}
	public Users get(String name,String psw){
		Users gb=new Users();
		Connection con=db.getConnection();
		String sql="select * from Users where username=? and password=?";
		PreparedStatement st = null;
		ResultSet rs=null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, name);
			st.setString(2, psw);
			rs=st.executeQuery();
			if(rs.next()){
				gb.setId(rs.getInt("id"));
				gb.setUsername(rs.getString("username"));
				gb.setPassword(rs.getString("password"));
			}else gb=null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return gb;
	}
	public List<Users> getAll(){
		Users gb=null;
		List<Users> list=new ArrayList<Users>();
		Connection con=db.getConnection();
		String sql = "select * from Users";
		Statement st = null;
		ResultSet rs=null;
		try {
			st = con.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next()){
				gb=new Users();
				gb.setId(rs.getInt("id"));
				gb.setUsername(rs.getString("username"));
				gb.setPassword(rs.getString("password"));
				list.add(gb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				try {
					st.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
}
